package com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.repository.Impl;

import com.css.fxfzxczh.constants.YNEnum;
import com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.entity.InvestigationTeam;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.entity.Notice;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.entity.NoticeRange;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.entity.Team;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.param.NoticePageParam;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.param.NoticeSaveParam;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.repository.NoticeRepository;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.vo.NoticeSaveParamVO;
import com.css.fxfzxczh.modules.fieldWorkManage.noticeManage.vo.TeamVO;
import com.css.fxfzxczh.modules.synthesizeInfoManage.houseStructureManagement.entity.HouseStructureManagement;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/02/02
 * @Description: 通知管理
 */
@Repository
public class NoticeRepositoryImpl implements NoticeRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "xczh_notice_management"; // 表名
    private static final String TABLE_ID = "xnm_id"; // 表id字段名

    @Override
    public Map<String, Object> getPage(NoticePageParam queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //通知名称
        if (!PlatformObjectUtils.isEmpty(queParams.getNoticeName())) {
            whereSql.append(" and notice_name like '%" + queParams.getNoticeName() + "%'");
        }
        //通知类型
        if (!PlatformObjectUtils.isEmpty(queParams.getNoticeType())) {
            whereSql.append(" and notice_type = '" + queParams.getNoticeType() + "'");
        }
        //地震事件id
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouseStructureManagement.class)).size();
        List<Notice> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(Notice.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<String> findAllByXsemId(String xsemId) {
        String sql = " select tel from xczh_personnel_info_management where del_flag = '0' and xsem_id = '" + xsemId + "'";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public void saveAll(ArrayList<NoticeRange> param) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(param.toArray());
        String sql = "insert into xczh_notice_range" +
                " (id,xnm_id,tel,del_flag)" +
                " values(:id,:xnmId,:tel,:delFlag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    /**
     * 根据地震事件id获取所有调查组组长tel
     *
     * @param xsemId
     * @return
     */
    @Override
    public List<String> queryLeaderTelByXsemId(String xsemId) {
        String sql = "select leader_phone from xczh_investigation_team where del_flag = '0' and xsem_id = '" + xsemId + "'";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 根据地震事件id获取所有调查组组员tel
     *
     * @param xsemId
     * @return
     */
    @Override
    public List<String> queryMemberTelByXsemId(String xsemId) {
        String sql = "select b.tel from " +
                "(SELECT xit_id FROM xczh_investigation_team where del_flag = '0' and xsem_id ='" + xsemId + "') a" +
                " left join " +
                "xczh_investigation_personnel b" +
                " on a.xit_id=b.investigation_id";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 根据调查组id获取该调查组所有组员tel
     *
     * @param xitId
     * @return
     */
    @Override
    public List<String> queryMemberTelById(String xitId) {
        String sql = "select b.tel from " +
                "(SELECT xit_id FROM xczh_investigation_team where del_flag = '0' and xit_id ='" + xitId + "') a" +
                " left join " +
                "xczh_investigation_personnel b" +
                " on a.xit_id=b.investigation_id";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public void save(NoticeSaveParam param) {
        String sql = "insert into " + TABLE_NAME +
                " (xnm_id,xsem_id,notice_code,notice_name," +
                "notice_type,notice_range,notice_content,sponsor,notice_time," +
                "del_flag,create_user,create_time)" +
                " values(:xnmId,:xsemId,:noticeCode,:noticeName," +
                ":noticeType,:noticeRange,:noticeContent,:sponsor,:noticeTime," +
                ":delFlag,:createUser,:createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteById(String id) {
        String sql = "update " + TABLE_NAME + " set del_flag = ?  where " + TABLE_ID + " = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(), id);
    }

    //删除通知范围
    @Override
    public void deleteRange(String id) {
        String sql = "update xczh_notice_range set del_flag = ?  where " + TABLE_ID + " = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(), id);
    }

    @Override
    public void updateById(NoticeSaveParam param) {
        String sql = "update " + TABLE_NAME +
                " set notice_name = :noticeName,notice_type = :noticeType," +
                "notice_range = :noticeRange,notice_content=:noticeContent,sponsor=:sponsor," +
                "notice_time=:noticeTime,update_user=:updateUser,update_time=:updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '" + param.getXnmId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public NoticeSaveParamVO findById(String id) {
        String sql = "select * from " + TABLE_NAME + " where " + TABLE_ID + " = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(NoticeSaveParamVO.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public String getCode(String xsemId) {
        String sql = "select notice_code from " + TABLE_NAME + " where xsem_id = '" + xsemId + "' and del_flag = '0' order by create_time desc limit 1";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public String queryTelByName(String id,String xsemId) {
        String sql = "select tel from xczh_personnel_info_management where del_flag = '0' and uuid ='" + id + "' and xsem_id = '" + xsemId + "'";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<InvestigationTeam> getItemAll(String xsemId) {
        String sql = "select * from xczh_investigation_team where del_flag = '0' and xsem_id = '" + xsemId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvestigationTeam.class));
    }

    @Override
    public void saveTeam(List<Team> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = " insert into  xczh_notice_item  (xnm_id,xit_id,team_code," +
                "leader,leader_phone,team_desc,company,team_name)" +
                "values (:xnmId,:xitId,:teamCode," +
                ":leader,:leaderPhone,:teamDesc,:company,:teamName)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void deleteTeam(String xnmId) {
        String sql = " delete from xczh_notice_item where xnm_id like '"+ xnmId +"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<TeamVO> queryTeam(String id) {
        String sql="select * from xczh_notice_item where xnm_id = '"+id+"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TeamVO.class));
    }

    @Override
    public String getXsemCode(String xsemId) {
        String sql = "select event_code from  xczh_seismic_event_management where  xsem_id = '" + xsemId + "' ";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}
